package com.ph.irp.base.verifyproject.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.verifyproject.dao.PBaseVerifyprojectDepartmentDao;
import com.ph.irp.model.PBaseVerifyprojectDepartment;
import com.ph.util.DBUtil;

public class PBaseVerifyprojectDepartmentDaoImpl implements
		PBaseVerifyprojectDepartmentDao {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	@Override
	public void addDepartment(PBaseVerifyprojectDepartment department)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb
				.append("insert into p_base_verifyproject_department(DepartmentCode,DepartmentName");
		if (department.getPBaseVerifyprojectCandiateId() != 0) {
			sb.append(",CandiateID");
		}
		if (department.getDepartmentPhone() != null
				&& !department.getDepartmentPhone().equals("")) {
			sb.append(",DepartmentPhone");
		}
		if (department.getDepartmentFax() != null
				&& !department.getDepartmentFax().equals("")) {
			sb.append(",DepartmentFax");
		}
		if (department.getPBaseAccountbookId() != 0) {
			sb.append(",accountbookId");
		}
		if (department.getMemo() != null && !department.getMemo().equals("")) {
			sb.append(",Memo");
		}
		sb.append(") values(");
		sb.append("'");
		sb.append(department.getDepartmentCode().trim());
		sb.append("'");
		sb.append(",'");
		sb.append(department.getDepartmentName().trim());
		sb.append("'");
		if (department.getPBaseVerifyprojectCandiateId() != 0) {
			sb.append(",");
			sb.append(department.getPBaseVerifyprojectCandiateId());
		}
		if (department.getDepartmentPhone() != null
				&& !department.getDepartmentPhone().equals("")) {
			sb.append(",'");
			sb.append(department.getDepartmentPhone().trim());
			sb.append("'");
		}
		if (department.getDepartmentFax() != null
				&& !department.getDepartmentFax().equals("")) {
			sb.append(",'");
			sb.append(department.getDepartmentFax().trim());
			sb.append("'");
		}
		if (department.getPBaseAccountbookId() != 0) {
			sb.append(",");
			sb.append(department.getPBaseAccountbookId());
		}
		if (department.getMemo() != null && !department.getMemo().equals("")) {
			sb.append(",'");
			sb.append(department.getMemo().trim());
			sb.append("'");
		}
		sb.append(")");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public void delateDepartment(int departmentId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		String sql = "delete from p_base_verifyproject_department where p_base_verifyproject_department.DepartmentID=?";

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, departmentId);
			pStmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	@Override
	public List<PBaseVerifyprojectDepartment> getAllDepartment()
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseVerifyprojectDepartment> list = new ArrayList<PBaseVerifyprojectDepartment>();

		String sql = "select * from p_base_verifyproject_department";

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseVerifyprojectDepartment department = new PBaseVerifyprojectDepartment();
				department.setDepartmentId(rs.getInt("DepartmentID"));
				department.setDepartmentCode(rs.getString("DepartmentCode"));
				department.setDepartmentName(rs.getString("DepartmentName"));
				department.setPBaseVerifyprojectCandiateId(rs.getInt("CandiateID"));
				department.setDepartmentPhone(rs.getString("DepartmentPhone"));
				department.setDepartmentFax(rs.getString("DepartmentFax"));
				department.setPBaseAccountbookId(rs.getInt("accountbookId"));
				department.setMemo(rs.getString("Memo"));
				list.add(department);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

	@Override
	public PBaseVerifyprojectDepartment getDepartmentById(int departmentId)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseVerifyprojectDepartment department = null;

		String sql = "select * from p_base_verifyproject_department where DepartmentID="+ departmentId;
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			rs = pStmt.executeQuery();

			if (rs.next()) {
				department = new PBaseVerifyprojectDepartment();
				department.setDepartmentId(rs.getInt("DepartmentID"));
				department.setDepartmentCode(rs.getString("DepartmentCode"));
				department.setDepartmentName(rs.getString("DepartmentName"));
				department.setPBaseVerifyprojectCandiateId(rs.getInt("CandiateID"));
				department.setDepartmentPhone(rs.getString("DepartmentPhone"));
				department.setDepartmentFax(rs.getString("DepartmentFax"));
				department.setPBaseAccountbookId(rs.getInt("accountbookId"));
				department.setMemo(rs.getString("Memo"));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return department;
	}

	@Override
	public boolean isSame(String departmentCode) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		String sql = "select * from p_base_verifyproject_department where DepartmentCode='"
				+ departmentCode.trim() + "'";
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			rs = pStmt.executeQuery();
			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public void updateDepartment(PBaseVerifyprojectDepartment department)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append("update p_base_verifyproject_department set DepartmentCode='");
		sb.append(department.getDepartmentCode().trim()).append("',DepartmentName='");
		sb.append(department.getDepartmentName().trim()).append("'");
		if (department.getPBaseVerifyprojectCandiateId() != 0) {
			sb.append(",CandiateID=").append(department.getPBaseVerifyprojectCandiateId());
		}
			sb.append(",DepartmentPhone='").append(department.getDepartmentPhone().trim()).append("'");

			sb.append(",DepartmentFax='").append(department.getDepartmentFax().trim()).append("'");

		if (department.getPBaseAccountbookId() != 0) {
			sb.append(",accountbookId=").append(department.getPBaseAccountbookId());
		}
		sb.append(",Memo='").append(department.getMemo().trim()).append("'");

		sb.append(" where DepartmentID=").append(department.getDepartmentId());

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List getDepartmentByCode(String code) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseVerifyprojectDepartment> list = new ArrayList<PBaseVerifyprojectDepartment>();

		StringBuilder sb = new StringBuilder();
		sb
				.append("select * from p_base_verifyproject_department where DepartmentCode like '%"
						+ code + "%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseVerifyprojectDepartment pbvd = new PBaseVerifyprojectDepartment();
				pbvd.setDepartmentId(rs.getInt("DepartmentID"));
				pbvd.setDepartmentCode(rs.getString("DepartmentCode"));
				pbvd.setDepartmentName(rs.getString("DepartmentName"));
				pbvd.setPBaseVerifyprojectCandiateId(rs.getInt("CandiateID"));
				pbvd.setDepartmentPhone(rs.getString("DepartmentPhone"));
				pbvd.setDepartmentFax(rs.getString("DepartmentFax"));
				pbvd.setPBaseAccountbookId(rs.getInt("accountbookId"));
				pbvd.setMemo(rs.getString("Memo"));
				list.add(pbvd);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;

	}

	@SuppressWarnings("unchecked")
	@Override
	public List getDepartmentByCodeAndName(String code, String name)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseVerifyprojectDepartment> list = new ArrayList<PBaseVerifyprojectDepartment>();

		StringBuilder sb = new StringBuilder();
		sb
				.append("select * from p_base_verifyproject_department where DepartmentCode like '%"
						+ code + "%' and DepartmentName like '%" + name + "%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseVerifyprojectDepartment pbvd = new PBaseVerifyprojectDepartment();
				pbvd.setDepartmentId(rs.getInt("DepartmentID"));
				pbvd.setDepartmentCode(rs.getString("DepartmentCode"));
				pbvd.setDepartmentName(rs.getString("DepartmentName"));
				pbvd.setPBaseVerifyprojectCandiateId(rs.getInt("CandiateID"));
				pbvd.setDepartmentPhone(rs.getString("DepartmentPhone"));
				pbvd.setDepartmentFax(rs.getString("DepartmentFax"));
				pbvd.setPBaseAccountbookId(rs.getInt("accountbookId"));
				pbvd.setMemo(rs.getString("Memo"));
				list.add(pbvd);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List getDepartmentByName(String name) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseVerifyprojectDepartment> list = new ArrayList<PBaseVerifyprojectDepartment>();

		StringBuilder sb = new StringBuilder();
		sb
				.append("select * from p_base_verifyproject_department where  DepartmentName like '%"
						+ name + "%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseVerifyprojectDepartment pbvd = new PBaseVerifyprojectDepartment();
				pbvd.setDepartmentId(rs.getInt("DepartmentID"));
				pbvd.setDepartmentCode(rs.getString("DepartmentCode"));
				pbvd.setDepartmentName(rs.getString("DepartmentName"));
				pbvd.setPBaseVerifyprojectCandiateId(rs.getInt("CandiateID"));
				pbvd.setDepartmentPhone(rs.getString("DepartmentPhone"));
				pbvd.setDepartmentFax(rs.getString("DepartmentFax"));
				pbvd.setPBaseAccountbookId(rs.getInt("accountbookId"));
				pbvd.setMemo(rs.getString("Memo"));
				list.add(pbvd);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

}
